// src/services/categoryService.ts
import pool from '../config/db';
import { Category } from '../types/category';
import { ResultSetHeader } from 'mysql2/promise';

// 辅助函数：构建分类路径
const buildPath = async (parent_id: number | null, name: string): Promise<string> => {
  if (!parent_id) {
    return `/${name}`;
  }
  const [rows] = await pool.query('SELECT path FROM Category WHERE id = ?', [parent_id]);
  const categories = rows as Category[];
  if (categories.length === 0) {
    throw new Error('Parent category not found');
  }
  return `${categories[0].path}/${name}`;
}; 

// 添加新分类
export const addCategory = async (name: string, parent_id: number | null = null, user_id: number, family_id: any): Promise<number> => {
  // 检查分类名称是否已存在
  const [existingRows] = await pool.query('SELECT * FROM Category WHERE name = ?', [name]);
  const existing = existingRows as Category[];
  if (existing.length > 0) {
    throw new Error('Category name already exists');
  }

  // 构建路径
  const path = await buildPath(parent_id, name);

  // 插入新分类
  const [result] = await pool.query(
    'INSERT INTO Category (name, parent_id, path, user_id, family_id) VALUES (?, ?, ?, ?, ?)',
    [name, parent_id, path, user_id, family_id]
  );

  const insertResult = result as ResultSetHeader;
  return insertResult.insertId;
}; 

// 查询所有分类
export const getAllCategories = async (): Promise<Category[]> => {
  const [rows] = await pool.query('SELECT * FROM Category ORDER BY path ASC');
  return rows as Category[];
};

// 查询单个分类
export const getCategoryById = async (family_id: number): Promise<Category | null> => {
  const [rows] = await pool.query('SELECT * FROM Category WHERE family_id = ?', [family_id]);
  const categories = rows as Category[];
  if (categories.length === 0) {
    return null;
  }
  return categories[0];
};

// 修改分类
export const updateCategory = async (id: number, name?: string, parent_id?: number | null): Promise<void> => {
  const connection = await pool.getConnection();
  try {
    await connection.beginTransaction();

    // 获取当前分类
    const [currentRows] = await connection.query('SELECT * FROM Category WHERE id = ?', [id]);
    const currentCategories = currentRows as Category[];
    if (currentCategories.length === 0) {
      throw new Error('Category not found');
    }
    const currentCategory = currentCategories[0];

    // 如果更新了名称，检查是否重复
    if (name && name !== currentCategory.name) {
      const [existingRows] = await connection.query('SELECT * FROM Category WHERE name = ?', [name]);
      const existing = existingRows as Category[];
      if (existing.length > 0) {
        throw new Error('Category name already exists');
      }
    }

    // 如果更新了 parent_id，检查其存在性和循环依赖
    if (parent_id !== undefined) {
      if (parent_id === id) {
        throw new Error('Category cannot be its own parent');
      }

      if (parent_id !== null) {
        const [parentRows] = await connection.query('SELECT * FROM Category WHERE id = ?', [parent_id]);
        const parentCategories = parentRows as Category[];
        if (parentCategories.length === 0) {
          throw new Error('Parent category not found');
        }

        // 检查是否将分类移动到其子分类下，防止循环
        if (parentCategories[0].path.startsWith(`${currentCategory.path}/`)) {
          throw new Error('Cannot move category to its own subcategory');
        }
      }
    }

    // 构建新的路径
    let newPath = currentCategory.path;
    if (name) {
      const parts = currentCategory.path.split('/');
      parts[parts.length - 1] = name;
      newPath = parts.join('/');
    }

    if (parent_id !== undefined) {
      newPath = await buildPath(parent_id, name || currentCategory.name);
    }

    // 更新分类
    await connection.query(
      'UPDATE Category SET name = ?, parent_id = ?, path = ? WHERE id = ?',
      [name || currentCategory.name, parent_id !== undefined ? parent_id : currentCategory.parent_id, newPath, id]
    );

    // 如果路径有变化，更新所有子分类的路径
    if (newPath !== currentCategory.path) {
      const oldPath = currentCategory.path;
      const [children] = await connection.query('SELECT * FROM Category WHERE path LIKE ?', [`${oldPath}/%`]);

      const childCategories = children as Category[];
      for (const child of childCategories) {
        const childNewPath = child.path.replace(oldPath, newPath);
        await connection.query('UPDATE Category SET path = ? WHERE id = ?', [childNewPath, child.id]);
      }
    }

    await connection.commit();
  } catch (error) {
    await connection.rollback();
    throw error;
  } finally {
    connection.release();
  }
};

// 删除分类 
export const deleteCategory = async (id: number): Promise<void> => {
  const connection = await pool.getConnection();
  try {
    await connection.beginTransaction();

    // 1. 验证分类是否存在
    const [currentRows] = await connection.query('SELECT * FROM Category WHERE id = ?', [id]);
    const currentCategories = currentRows as Category[];
    if (currentCategories.length === 0) {
      throw new Error('Category not found');
    }
    const currentCategory = currentCategories[0];

    // 2. 检查是否有子分类
    const [childRows] = await connection.query('SELECT * FROM Category WHERE parent_id = ?', [id]);
    const childCategories = childRows as Category[];
    if (childCategories.length > 0) {
      throw new Error('Cannot delete category with subcategories');
    }

    // 3. 检查是否有关联的数据（例如，作品、产品等）
    // 假设有一个作品表 Work，其中有 category_id 字段关联分类
    const [workRows] = await connection.query('SELECT * FROM items WHERE category_id = ?', [id]);
    const works = workRows as any[]; // 根据实际情况定义类型
    if (works.length > 0) {
      throw new Error('Cannot delete category with associated works');
    }

    // 4. 删除分类
    await connection.query('DELETE FROM Category WHERE id = ?', [id]);

    // 5. 提交事务
    await connection.commit();
  } catch (error) {
    // 发生错误时回滚事务
    await connection.rollback();
    throw error;
  } finally {
    // 释放连接
    connection.release();
  }
};
